package utl.mofen.impl;

import java.sql.*;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;

import org.apache.log4j.Logger;
import org.apache.tomcat.dbcp.dbcp.BasicDataSource;

import utl.mofen.intf.IDBOperator;
import utl.mofen.utils.GUID;

/**
 * 读取web下面的配置问题/META-INF/context.xml
 * @author Administrator
 *
 */
public class DBOperator implements IDBOperator {
	private static final Logger logger = Logger.getLogger(DBOperator.class);

	private Boolean __autoCommitState;
	private String __instanceKey = "";
	Connection conn = null;
	Statement sts = null;
	Statement execsts = null;
	
	//批量执行SQL
	PreparedStatement preSts = null;
	
	String dbUrl = null;
	
	public String getInstanceKey()
	{
		if (__instanceKey.equals(""))
		{		
			__instanceKey = GUID.createGUID();
		}
		return __instanceKey;		
	}
	
	public DBOperator()
	{
				
	}
	
	public static String poolNumActive(String key)
	{
		String info="";
		try
		{
			Context initCtx = new InitialContext();
			Context envCtx = (Context)initCtx.lookup("java:comp/env");
			
			
			BasicDataSource ds = null;
			if (key==""||key==null)
			{
			   ds =(BasicDataSource)envCtx.lookup("main");
			}else
			{
			   ds =(BasicDataSource)envCtx.lookup(key);
			}
			info = String.valueOf(ds.getNumActive());
		}catch(Exception e)
		{
			e.printStackTrace();
			info = e.getMessage();
		}		
		return info;
	}
	
	public static String poolINfo(String key)
	{
		String info="";
		try
		{
			Context initCtx = new InitialContext();
			Context envCtx = (Context)initCtx.lookup("java:comp/env");
			
			
			BasicDataSource ds = null;
			if (key==""||key==null)
			{
			   ds =(BasicDataSource)envCtx.lookup("main");
			}else
			{
			   ds =(BasicDataSource)envCtx.lookup(key);
			}
			info = "当前连接数:" + ds.getNumActive();
			logger.info(info);
		}catch(Exception e)
		{
			e.printStackTrace();
			info = e.getMessage();
		}		
		return info;
	}
	
	public DBOperator(Connection __conn) throws SQLException
	{
		conn=__conn;		
  	    sts = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE ,java.sql.ResultSet.CONCUR_READ_ONLY);
		execsts = conn.createStatement();		
	}

	public DBOperator(String key) throws Exception {
		Context initCtx = new InitialContext();
		Context envCtx = (Context)initCtx.lookup("java:comp/env");
		
		
		BasicDataSource ds = null;
		if (key==""||key==null)
		{
		   ds =(BasicDataSource)envCtx.lookup("main");
		}else
		{
		   ds =(BasicDataSource)envCtx.lookup(key);
		}
		
//		DataSource ds = null;
//		if (key==""||key==null)
//		{
//		   ds =(DataSource)envCtx.lookup("main");
//		}else
//		{
//		   ds =(DataSource)envCtx.lookup(key);
//		}
		
		if (ds==null)
		{
			throw new Exception("没有找到" + key +"对应的连接池配置!");
		}			
		conn=ds.getConnection();
		
		logger.info("当前连接池数量:"+ ds.getNumActive());
		

		sts = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE ,java.sql.ResultSet.CONCUR_READ_ONLY);
		execsts = conn.createStatement();
	}
	
	/**
	 * 关闭连接
	 * @throws SQLException 
	 */
	public void close() throws SQLException
	{
		sts.close();
		//preSts.close();
		execsts.close();
		conn.close();
	}
	
	
	/**
	 * 添加SQL到批量执行计划中
	 * @param sql
	 * 要添加的SQL
	 * @throws SQLException 
	 */
	public void addSQL4BatchExecute(String sql) throws SQLException
	{
		execsts.addBatch(sql);
	}
	
	/**
	 * 清理批量执行的SQL
	 * @throws SQLException 
	 */
	public void clearBatch() throws SQLException
	{
		execsts.clearBatch();
	}

	public static DBOperator getInstance(String key)
			throws Exception {
		return new DBOperator(key);
	}
	
	public ResultSet queryData(String sql) throws SQLException {
		return sts.executeQuery(sql);
	}
	
	public String executeAsString(String sql) throws SQLException
	{
		Statement tempsts = conn.createStatement();
		ResultSet rs = tempsts.executeQuery(sql);
		try {
			if (rs.next()) {
				return rs.getString(1);
			} else {
				return "";
			}
		} finally {
			rs.close();
			tempsts.close();
		}

	}
	
	public double executeAsNumeric(String sql) throws SQLException
	{
		Statement tempsts = conn.createStatement();
		ResultSet rs = tempsts.executeQuery(sql);
		try {
			if (rs.next()) {
				return rs.getDouble(1);
			} else {
				return new Double(0);
			}
		} finally {
			rs.close();
			tempsts.close();
		}
	}
	
	public int executeAsInteger(String sql) throws SQLException
	{
		Statement tempsts = conn.createStatement();
		ResultSet rs = tempsts.executeQuery(sql);
		try {
			if (rs.next()) {
				return rs.getInt(1);
			} else {
				return 0;
			}
		} finally {
			rs.close();
			tempsts.close();
		}
	}
	
	/**
	 * 使用事务开关执行语句
	 * @param sql
	 * @param withTran
	 * @throws SQLException
	 */
	public void executeSQLWithTrans(String sql, Boolean withTran) throws SQLException
	{
		if (withTran) this.beginTrans("dbOperator.executeSQLWithTrans");
		try
		{
			executeSQL(sql);
			if (withTran) this.commitTrans("dbOperator.executeSQLWithTrans");
		}catch(SQLException e)
		{
			if (withTran) this.rollBack("dbOperator.executeSQLWithTrans");
			throw e;
		}
	}
	
	/**
	 * 执行SQL语句没有返回(可以执行没有返回的sql,和有返回的sql)
	 * @param sql
	 * @throws SQLException
	 */
	public int executeSQL(String sql) throws SQLException{		
		//execsts.executeUpdate(sql);
		execsts.execute(sql);
		return execsts.getUpdateCount(); 
	}
	
	//开启事务
	public void beginTrans(String msg) throws SQLException
	{
		__autoCommitState = conn.getAutoCommit();
		conn.setAutoCommit(false);
		logger.info("[开启事务]" + msg);
	}
	
	//回滚事务
	public void rollBack(String msg) throws SQLException
	{
		conn.rollback();
		conn.setAutoCommit(__autoCommitState);
		logger.info("[回滚事务]" + msg);
	}
	
	//提交事务
	public void commitTrans(String msg) throws SQLException
	{
		conn.commit();
		conn.setAutoCommit(__autoCommitState);
		logger.info("[提交事务]" + msg);
	}
	
	//获取表的字段名
	public String getTableFields(String table) throws SQLException
	{
		String sql="select * from " + table + " where 1=0";
		ResultSet rs = queryData(sql);
		ResultSetMetaData rsmd=rs.getMetaData();
		String s = "";
		
		for (int i=1; i<=rsmd.getColumnCount();i++)
		{			
			s = s + rsmd.getColumnName(i) + ",";			
		}
		
		//截掉最后的空格
		if (s!="") s=s.substring(0, s.length()-1);
		return s;
	}

	/**
	 * 添加一个SQL数组到批量执行中
	 * @param sqlList
	 *   字符串SQL数组
	 * @throws SQLException
	 */
	public void addSQLs4BatchExecute(ArrayList<String> sqlList)
			throws SQLException {
		if (sqlList!=null)
		{
			for (int i=0; i<sqlList.size();i++)
			{
				addSQL4BatchExecute(sqlList.get(i));
			}
		}
	}


	public void executeBatchSQL() throws SQLException {
		//本身不带事务
		execsts.executeBatch();
	}
	
	
	public void executeBatchSQLWithTran(String msg) throws SQLException {
		this.beginTrans(msg);
		try
		{
			executeBatchSQL();
			this.commitTrans(msg);
		}catch(SQLException e)
		{
			this.rollBack(msg);
			throw e;
		};	
	}

	public boolean canUseable() {
		// TODO Auto-generated method stub
		return false;
	}

	public Connection getConnection() {
		return this.conn;
	}
}
